package com.yunfeisoft.business.dao.impl.postgres;

import com.applet.base.ServiceDaoImpl;
import com.applet.sql.builder.SelectBuilder;
import com.applet.sql.builder.WhereBuilder;
import com.applet.sql.mapper.DefaultRowMapper;
import com.applet.utils.DateUtils;
import com.applet.utils.Page;
import com.yunfeisoft.business.dao.inter.PurchaseOrderDao;
import com.yunfeisoft.business.model.PurchaseOrder;
import com.yunfeisoft.business.model.PurchaseOrderStatistics;
import com.yunfeisoft.business.model.Supplier;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * ClassName: PurchaseOrderDaoImpl
 * Description: 采购单信息Dao实现
 * Author: Jackie liu
 * Date: 2020-07-23
 */
@Repository
public class PurchaseOrderDaoImpl extends ServiceDaoImpl<PurchaseOrder, String> implements PurchaseOrderDao {

    @Override
    public Page<PurchaseOrder> queryPage(Map<String, Object> params) {
        WhereBuilder wb = new WhereBuilder();
        wb.setOrderByWithDesc("po.createTime");
        if (params != null) {
            initPageParam(wb, params);
            wb.andEquals("po.orgId", params.get("orgId"));
            wb.andEquals("po.payStatus", params.get("payStatus"));
            wb.andFullLike("s.name", params.get("supplierName"));
        }

        SelectBuilder builder = getSelectBuilder("po");
        builder.column("s.name as supplierName")
                .join(Supplier.class).alias("s").on("po.supplierId = s.id");

        return queryPage(builder.getSql(), wb);
    }

    @Override
    public List<PurchaseOrderStatistics> queryStatisticsByProductCategory(String orgId, String beginDate, String endDate) {
        String sql = "SELECT PC.ID_ AS ID_, PC.NAME_ AS NAME_, SUM(PI.AMOUNT_) AS TOTAL_AMOUNT_ FROM TT_PURCHASE_ITEM PI " +
                "JOIN TT_PURCHASE_ORDER PO ON PI.PURCHASE_ORDER_ID_ = PO.ID_ JOIN TT_PRODUCT P ON PI.PRODUCT_ID_ = P.ID_ JOIN TT_PRODUCT_CATEGORY PC ON P.CATEGORY_ID_ = PC.ID_ " +
                "WHERE PI.IS_DEL_ = 2 AND PO.STATUS_ = ? AND PI.ORG_ID_ = ? AND PO.PURCHASE_DATE_ >= ? AND PO.PURCHASE_DATE_ <= ? GROUP BY PC.ID_, PC.NAME_";
        return jdbcTemplate.query(sql, new Object[]{PurchaseOrder.PurchaseOrderStatusEnum.STORAGED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<PurchaseOrderStatistics>(PurchaseOrderStatistics.class));
    }

    @Override
    public List<PurchaseOrderStatistics> queryStatisticsByProduct(String orgId, String beginDate, String endDate) {
        String sql = "SELECT P.ID_ AS ID_, P.NAME_ AS NAME_, SUM(PI.AMOUNT_) AS TOTAL_AMOUNT_ FROM TT_PURCHASE_ITEM PI JOIN TT_PURCHASE_ORDER PO ON PI.PURCHASE_ORDER_ID_ = PO.ID_ JOIN TT_PRODUCT P ON PI.PRODUCT_ID_ = P.ID_ " +
                "WHERE PI.IS_DEL_ = 2 AND PO.STATUS_ = ? AND PI.ORG_ID_ = ? AND PO.PURCHASE_DATE_ >= ? AND PO.PURCHASE_DATE_ <= ? GROUP BY P.ID_, P.NAME_";
        return jdbcTemplate.query(sql, new Object[]{PurchaseOrder.PurchaseOrderStatusEnum.STORAGED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<PurchaseOrderStatistics>(PurchaseOrderStatistics.class));
    }

    @Override
    public List<PurchaseOrderStatistics> queryStatisticsBySupplier(String orgId, String beginDate, String endDate) {
        String sql = "SELECT PO.SUPPLIER_ID_ AS ID_, S.NAME_ AS NAME_, SUM(PO.TOTAL_AMOUNT_) AS TOTAL_AMOUNT_ FROM TT_PURCHASE_ORDER PO JOIN TT_SUPPLIER S ON PO.SUPPLIER_ID_ = S.ID_ " +
                "WHERE PO.IS_DEL_ = 2 AND PO.STATUS_ = ? AND PO.ORG_ID_ = ? AND PO.PURCHASE_DATE_ >= ? AND PO.PURCHASE_DATE_ <= ? GROUP BY PO.SUPPLIER_ID_, S.NAME_";
        return jdbcTemplate.query(sql, new Object[]{PurchaseOrder.PurchaseOrderStatusEnum.STORAGED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<PurchaseOrderStatistics>(PurchaseOrderStatistics.class));
    }

    @Override
    public List<PurchaseOrderStatistics> queryStatisticsByCreateId(String orgId, String beginDate, String endDate) {
        String sql = "SELECT U.ID_ AS ID_, U.NAME_ AS NAME_, SUM(PO.TOTAL_AMOUNT_) AS TOTAL_AMOUNT_ FROM TT_PURCHASE_ORDER PO JOIN TS_USER U ON PO.CREATE_ID_ = U.ID_ " +
                "WHERE PO.IS_DEL_ = 2 AND PO.STATUS_ = ? AND PO.ORG_ID_ = ? AND PO.PURCHASE_DATE_ >= ? AND PO.PURCHASE_DATE_ <= ? GROUP BY U.ID_, U.NAME_";
        return jdbcTemplate.query(sql, new Object[]{PurchaseOrder.PurchaseOrderStatusEnum.STORAGED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<PurchaseOrderStatistics>(PurchaseOrderStatistics.class));
    }

    @Override
    public List<PurchaseOrderStatistics> queryStatisticsByDate(String orgId, String beginDate, String endDate) {
        String sql = "SELECT TO_CHAR(PURCHASE_DATE_, 'YYYY-MM-DD') AS DAY_, COUNT(1) AS PURCHASE_NUM_, SUM(TOTAL_AMOUNT_) AS TOTAL_AMOUNT_ FROM TT_PURCHASE_ORDER WHERE IS_DEL_ = 2 AND STATUS_ = ? AND ORG_ID_ = ? AND PURCHASE_DATE_ >= ? AND PURCHASE_DATE_ <= ? GROUP BY DAY_";
        return jdbcTemplate.query(sql, new Object[]{PurchaseOrder.PurchaseOrderStatusEnum.STORAGED.getValue(), orgId, DateUtils.getTimestamp(beginDate), DateUtils.getTimestamp(endDate)}, new DefaultRowMapper<PurchaseOrderStatistics>(PurchaseOrderStatistics.class));
    }

    @Override
    public PurchaseOrder queryTotalAmount(String orgId, int status, int payStatus) {
        String sql = "SELECT SUM(TOTAL_AMOUNT_) AS TOTAL_AMOUNT_, SUM(PAY_AMOUNT_) AS PAY_AMOUNT_ FROM TT_PURCHASE_ORDER WHERE ORG_ID_ = ? AND STATUS_ = ? AND PAY_STATUS_ = ? AND IS_DEL_ = 2";
        List<PurchaseOrder> list = jdbcTemplate.query(sql, new Object[]{orgId, status, payStatus}, new DefaultRowMapper<PurchaseOrder>(PurchaseOrder.class));
        return CollectionUtils.isEmpty(list) ? new PurchaseOrder() : list.get(0);
    }
}